﻿CREATE VIEW [dbo].[StandardSailQuote_View]
AS
SELECT     TOP (100) PERCENT QLI.QuoteLineItemID, QLI.QuoteID, QLI.LineItem, QLI.QuoteSailtypeID, QLI.Luff, QLI.LPorFoot, QLI.InputArea, QLI.GenericYarnID, 
                      QLI.BoatSpeed, QLI.AWS, QLI.FilmTypeID, QLI.DPIID, QLI.CurrencyID, QLI.Discount, QLI.TierID, QLI.UsageID, QLI.Createdate, QLI.ConstructionID, 
                      QLI.Price, QLI.SailModelCode, QLI.Cut, QLI.Weight, QLI.Multiple, QLI.TotalPrice, QLI.TotalOfferedPrice, QLI.AccessoryID, QLI.PreDiscountPrice, 
                      QLI.SailNumber, QLI.SailNumberColourID, QLI.AccessoryColourID, QLI.NylonColourDetails, QLI.FixedOfferPrice, QLI.BRP, QLI.ExRate AS expr2, 
                      QLI.FabricID, QLI.NewBRP, QLI.NotionalArea, QLI.AccessAttachLineitem, QLI.TopSurfaceID, QLI.BotSurfaceID, QLI.AccessoryItemID, QLI.DKKPrice, 
                      QLI.MarketPriceEuro, QLI.MarketRetailPrice, QLI.SeasonalPriceEuro, QLI.SeasonalPriceCurr, QLI.OptionTotal, QLI.BillingPrice, QLI.BillingDiscount, 
                      QLI.BillingCurrency, QLI.OEMSailListID, QLI.OriginatingOEMSailListID, dbo.Quote_Tbl.BoatGenID, dbo.Quote_Tbl.CurrencyID AS Expr1, 
                      dbo.Quote_Tbl.DeliveryWeek, QLI.Price AS FirstPrice, QLI.Multiple AS NumberQuoted,
                          (SELECT     Notes
                            FROM          dbo.OEMSailList_Tbl
                            WHERE      (OEMSailListID = QLI.OriginatingOEMSailListID)) AS Notes,
                          (SELECT     ExRate
                            FROM          dbo.Currency_Tbl
                            WHERE      (CurrencyID = dbo.Quote_Tbl.CurrencyID)) AS ExRate,
                          (SELECT     PriceFactor
                            FROM          dbo.SeasonalPrice_Tbl
                            WHERE      (WeekNo = dbo.Quote_Tbl.DeliveryWeek)) AS SeasonalPF,
                          (SELECT     CASE WHEN o.OEMIdentifier IS NOT NULL THEN o.OEMIdentifier ELSE '' END + ' - ' + CASE WHEN o.QuoteSailTypeID > 0 THEN
                                                       (SELECT     q.SailType
                                                         FROM          QuoteSailType_Tbl q
                                                         WHERE      q.QuoteSailTypeID = o.QuoteSailTypeID) ELSE CASE WHEN o.OptionID > 0 THEN
                                                       (SELECT     Option_Tbl.OptionName
                                                         FROM          Option_Tbl
                                                         WHERE      Option_Tbl.OptionID = o.OptionID) ELSE CASE WHEN o.AccessoryID <> 99999 THEN
                                                       (SELECT     Accessory_Tbl.Accessory
                                                         FROM          Accessory_Tbl
                                                         WHERE      Accessory_Tbl.AccessoryID = o.AccessoryID) ELSE '' END END END + ' - ' + CASE WHEN o.FabricID > 0 THEN
                                                       (SELECT     Fabric_Tbl.PartCode
                                                         FROM          Fabric_Tbl
                                                         WHERE      Fabric_Tbl.FabricID = o.FabricID) ELSE '' END AS Expr1
                            FROM          dbo.OEMSailList_Tbl AS o
                            WHERE      (QLI.OriginatingOEMSailListID = OEMSailListID)) AS OEMIdentifier, dbo.Quote_Tbl.Address1, dbo.Quote_Tbl.Address2, 
                      dbo.Quote_Tbl.Address3, dbo.Quote_Tbl.Postcode, dbo.Quote_Tbl.City, dbo.Quote_Tbl.CountryID, dbo.Quote_Tbl.Telephone, dbo.Quote_Tbl.Fax, 
                      dbo.Quote_Tbl.Email, dbo.Quote_Tbl.VAT, dbo.Quote_Tbl.FirstName, dbo.Quote_Tbl.LastName, dbo.Quote_Tbl.CookieID, QLI.AttachedToLineItem, 
                      QLI.HasAttachment
FROM         dbo.QuoteLineItem_Tbl AS QLI INNER JOIN
                      dbo.Quote_Tbl ON QLI.QuoteID = dbo.Quote_Tbl.QuoteID
ORDER BY QLI.QuoteID